package com.ghck.shop.dao.provider;

import java.text.MessageFormat;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import com.alibaba.druid.sql.ast.statement.SQLIfStatement.Else;
import com.ghck.shop.constant.GoodStatus;
import com.ghck.shop.model.Goods;
import com.ghck.shop.model.GoodsImage;
import com.ghck.shop.model.request.GoodsReq;


/** 
 * @author 作者 : chenxuan
 * @version 创建时间：2016年1月8日 上午11:10:36 
 * 类说明 
 */
public class GoodsMapperProvider {
	
	public String goodsHomeList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select id,qr_code,title,model,subject,market_price,sale_price,is_hot,is_recommend,thumbnail_image,");
		sBuffer.append("description from goods where is_onsale in (3,5)  and is_home_page = 1 order by sort_order desc");
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String deleteGoodsImageByGoodIdAndNotIds(Map<String, Object> map){
		StringBuffer sb = new StringBuffer();
		List<Integer> ids = (List<Integer>)map.get("ids");
		MessageFormat messageFormat = new MessageFormat("#'{'ids[{0}]}");
		sb.append("delete from goods_image  where goods_id=#{goodId} and id not in ");
		sb.append("(");
		int len = ids.size();
		for(int i=0;i<len;i++){
			sb.append(messageFormat.format(new Integer[]{i}));
			sb.append(",");
		}
		sb.setLength(sb.length() - 1);
		sb.append(")");
		return sb.toString();
	}
	
	public String getCategoryByIds(Map<String, List<Integer>> map){
		StringBuffer sb = new StringBuffer();
		List<Integer> serviceCardCodes = map.get("list");
		MessageFormat messageFormat = new MessageFormat("#'{'list[{0}]}");
		sb.append("select id,title,image from category where id in ");
		sb.append("(");
		int len = serviceCardCodes.size();
		for(int i=0;i<len;i++){
			sb.append(messageFormat.format(new Integer[]{i}));
			sb.append(",");
		}
		sb.setLength(sb.length() - 1);
		sb.append(")");
		sb.append(" order by sort_order desc");
		return sb.toString();
	}
	
	public String batchFindGoodByIds(Map<String, List<Integer>> map){
		StringBuffer sb = new StringBuffer();
		List<Integer> serviceCardCodes = map.get("list");
		MessageFormat messageFormat = new MessageFormat("#'{'list[{0}]}");
		sb.append("select id,qr_code,title,model,subject,market_price,sale_price,is_hot,is_recommend,thumbnail_image,");
		sb.append("description from goods where is_onsale in (3,5)   and id in");
		sb.append("(");
		int len = serviceCardCodes.size();
		for(int i=0;i<len;i++){
			sb.append(messageFormat.format(new Integer[]{i}));
			sb.append(",");
		}
		sb.setLength(sb.length() - 1);
		sb.append(")");
		return sb.toString();
	}
	
	public String goodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" left join goods_category gc on g.id = gc.goods_id ");
		}
		if(StringUtils.isNotEmpty(goodsReq.getKeyword())){
			sBuffer.append(" left join t_seller ts on g.supplier_id = ts.id ");
		}
		
		sBuffer.append(" where g.is_onsale in ( 3,5 )");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and gc.category_id = #{categoryId} ");
		}
		if(StringUtils.isNotEmpty(goodsReq.getKeyword())){
			sBuffer.append(" and (");
			sBuffer.append("  g.title like '%"+goodsReq.getKeyword().trim()+"%'");
			sBuffer.append(" or ts.shop_name like '%" + goodsReq.getKeyword().trim() + "%'");
			sBuffer.append(" ) ");
		}
		if(goodsReq.getSortConfigId() != null){
			/** 1:综合排序 2:销量升序 3:销量降序 4:价格升序 5:价格降序 **/
			if(goodsReq.getSortConfigId() == 1){
				sBuffer.append(" order by g.sort_order desc");
			}
			else if(goodsReq.getSortConfigId() == 2){
				sBuffer.append(" order by g.buy_count asc");
			}
			else if(goodsReq.getSortConfigId() == 3){
				sBuffer.append(" order by g.buy_count desc");
			}
			else if(goodsReq.getSortConfigId() == 4){
				sBuffer.append(" order by g.sale_price asc");
			}
			else if(goodsReq.getSortConfigId() == 5){
				sBuffer.append(" order by g.sale_price desc");
			}
		}else{
			sBuffer.append(" order by g.sort_order desc");	
		}
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String goodsCollectList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select gc.id collect_id,g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ,t_good_collect gc where g.id=gc.good_id");
		
		sBuffer.append(" and gc.user_id=#{userId}  ");
		if(StringUtils.isNotEmpty(goodsReq.getKeyword())){
			sBuffer.append(" and g.title like '%"+goodsReq.getKeyword().trim()+"%'");
		}
		sBuffer.append(" order by gc.update_time desc");	
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String medtronicGoodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" left join goods_category gc on g.id = gc.goods_id ");
		}
		sBuffer.append(" where 1 = 1 ");
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and gc.category_id = #{categoryId} ");
		}
		if(goodsReq.getSortConfig() != null){
			sBuffer.append(goodsReq.getSortConfig());
		}else{
			sBuffer.append("order by g.sort_order desc");	
		}
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String marketingActivityGoodsList(final GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select g.id,g.qr_code,g.title,g.model,g.subject,g.market_price,g.sale_price,g.is_hot,g.is_recommend,thumbnail_image,");
		sBuffer.append("g.description,g.intr_url from goods g ,marketing_activity_goods b where g.id = b.goods_id and b.marketing_activity_id = #{marketingActivityId}");
		sBuffer.append(" and g.is_onsale in (3,5)  ");
		if(goodsReq.getSortConfig() != null){
			sBuffer.append(goodsReq.getSortConfig());
		}else{
			sBuffer.append(" order by g.sort_order desc");	
		}
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String managerList(GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select * from goods a where 1 = 1 ");
		if(goodsReq.getKeyword() != null && !goodsReq.getKeyword().trim().isEmpty()){
			sBuffer.append(" and a.title like concat('%',#{keyword},'%') ");
		}
		if(goodsReq.getIsOnsale() != null){
			sBuffer.append(" and a.is_onsale = #{isOnsale} ");
		}
		if(goodsReq.getCategoryId() != null && goodsReq.getCategoryId() != 0){
			sBuffer.append(" and exists ( select * from category b,goods_category c where b.id = c.category_id and b.id = #{categoryId} and a.id = c.goods_id ) ");
		}
		sBuffer.append(" order by a.last_modify_time desc  ");
		if(goodsReq.getLimit() != null && goodsReq.getLimit() !=0){
			sBuffer.append(" limit #{start},#{limit}");
		}
		return sBuffer.toString();
	}
	
	public String countManagerList(GoodsReq goodsReq){
		StringBuffer sBuffer = new StringBuffer();
		sBuffer.append("select count(*) from goods a where 1 = 1 ");
		if(goodsReq.getTitle() != null && !goodsReq.getTitle().trim().isEmpty()){
			sBuffer.append(" and a.title like concat('%',#{title},'%') ");
		}
		if(goodsReq.getIsOnsale() != null){
			sBuffer.append(" and a.is_onsale = #{isOnsale} ");
		}
		return sBuffer.toString();
	}
	
	public String batchSaveGoodsImages(Map<String, List<GoodsImage>> map){
		StringBuffer sb = new StringBuffer();
		List<GoodsImage> serviceCardItems = map.get("list");
		sb.append("insert into goods_image(goods_id,image_small,image_large,sort_order,status) values");
	    MessageFormat messageFormat = new MessageFormat("("
	    		+ "#'{'list[{0}].goodsId},"
	    		+ "#'{'list[{0}].imageSmall},"
	    		+ "#'{'list[{0}].imageLarge},"
	    		+ "#'{'list[{0}].sortOrder},"
	    		+ "#'{'list[{0}].status})");
		int len = serviceCardItems.size();
		for(int i=0;i<len;i++){
	    	sb.append(messageFormat.format(new Integer[]{i}));
            sb.append(",");
	    }
	    sb.setLength(sb.length() - 1);
		return sb.toString();
	}
	
	public String batchSaveGoodsCategory(Map<String, Object> map){
		StringBuffer sb = new StringBuffer();
		List<Integer> categoryIds = (List<Integer>) map.get("categoryIds");
		sb.append("insert into goods_category(category_id,goods_id,create_time,last_modify_time) values");
	    MessageFormat messageFormat = new MessageFormat("("
	    		+ "#'{'categoryIds[{0}]},"
	    		+ "#'{'goodId},"
	    		+ "now(),"
	    		+ "now())");
		int len = categoryIds.size();
		for(int i=0;i<len;i++){
	    	sb.append(messageFormat.format(new Integer[]{i}));
            sb.append(",");
	    }
	    sb.setLength(sb.length() - 1);
		return sb.toString();
	}
	
	public String saveGoods(Goods goods){
		StringBuilder sb = new StringBuilder();
		sb.append("insert into goods(create_time,last_modify_time");
		if(goods.getQrCode()!=null){
			sb.append(",qr_code");
		}
		if(goods.getCatId()!=null){
			sb.append(",cat_id");
		}
		if(goods.getTitle()!=null){
			sb.append(",title");
		}
		if(goods.getModel()!=null){
			sb.append(",model");
		}
		if(goods.getSku()!=null){
			sb.append(",sku");
		}
		if(goods.getSupplierId()!=null){
			sb.append(",supplier_id");
		}
		if(goods.getStockId()!=null){
			sb.append(",stock_id");
		}
		if(goods.getLocation()!=null){
			sb.append(",location");
		}
		if(goods.getSubject()!=null){
			sb.append(",subject");
		}
		if(goods.getDescription()!=null){
			sb.append(",description");
		}
		if(goods.getMarketPrice()!=null){
			sb.append(",market_price");
		}
		if(goods.getSalePrice()!=null){
			sb.append(",sale_price");
		}
		if(goods.getCostPrice()!=null){
			sb.append(",cost_price");
		}
		if(goods.getPercentageRate()!=null){
			sb.append(",percentage_rate");
		}
		if(goods.getAmount()!=null){
			sb.append(",amount");
		}
		if(goods.getIsReal()!=null){
			sb.append(",is_real");
		}
		if(goods.getSortOrder()!=null){
			sb.append(",sort_order");
		}
		if(goods.getIsHot()!=null){
			sb.append(",is_hot");
		}
		if(goods.getIsRecommend()!=null){
			sb.append(",is_recommend");
		}
		if(goods.getThumbnailImage()!=null){
			sb.append(",thumbnail_image");
		}
		if(goods.getStockStatus()!=null){
			sb.append(",stock_status");
		}
		if(goods.getIsOnsale()!=null){
			sb.append(",is_onsale");
		}
		if(goods.getSubtract()!=null){
			sb.append(",subtract");
		}
		if(goods.getPostFree()!=null){
			sb.append(",post_free");
		}
		if(goods.getIsHomePage()!=null){
			sb.append(",is_home_page");
		}
		if(goods.getBuyCount()!=null){
			sb.append(",buy_count");
		}
		if(goods.getIntrUrl()!=null){
			sb.append(",intr_url");
		}
		if(goods.getIsExpress()!=null){
			sb.append(",is_express");
		}
		if(goods.getGoodsType()!=null){
			sb.append(",goods_type");
		}
		if(goods.getIsUseCoupon()!=null){
			sb.append(",is_use_coupon");
		}
		if(goods.getManufacturer()!=null){
			sb.append(",manufacturer");
		}
		if(goods.getApprovalNumber()!=null){
			sb.append(",approval_number");
		}
		if(goods.getBrand()!=null){
			sb.append(",brand");
		}
		if(goods.getMaterial()!=null){
			sb.append(",material");
		}
		if(goods.getColour()!=null){
			sb.append(",colour");
		}
		sb.append(") values ( now(),now() ");
		
		if(goods.getQrCode()!=null){
			sb.append(",#{qrCode}");
		}
		if(goods.getCatId()!=null){
			sb.append(",#{catId}");
		}
		if(goods.getTitle()!=null){
			sb.append(",#{title}");
		}
		if(goods.getModel()!=null){
			sb.append(",#{model}");
		}
		if(goods.getSku()!=null){
			sb.append(",#{sku}");
		}
		if(goods.getSupplierId()!=null){
			sb.append(",#{supplierId}");
		}
		if(goods.getStockId()!=null){
			sb.append(",#{stockId}");
		}
		if(goods.getLocation()!=null){
			sb.append(",#{location}");
		}
		if(goods.getSubject()!=null){
			sb.append(",#{subject}");
		}
		if(goods.getDescription()!=null){
			sb.append(",#{description}");
		}
		if(goods.getMarketPrice()!=null){
			sb.append(",#{marketPrice}");
		}
		if(goods.getSalePrice()!=null){
			sb.append(",#{salePrice}");
		}
		if(goods.getCostPrice()!=null){
			sb.append(",#{costPrice}");
		}
		if(goods.getPercentageRate()!=null){
			sb.append(",#{percentageRate}");
		}
		if(goods.getAmount()!=null){
			sb.append(",#{amount}");
		}
		if(goods.getIsReal()!=null){
			sb.append(",#{isReal}");
		}
		if(goods.getSortOrder()!=null){
			sb.append(",#{sortOrder}");
		}
		if(goods.getIsHot()!=null){
			sb.append(",#{isHot}");
		}
		if(goods.getIsRecommend()!=null){
			sb.append(",#{isRecommend}");
		}
		if(goods.getThumbnailImage()!=null){
			sb.append(",#{thumbnailImage}");
		}
		if(goods.getStockStatus()!=null){
			sb.append(",#{stockStatus}");
		}
		if(goods.getIsOnsale()!=null){
			sb.append(",#{isOnsale}");
		}
		if(goods.getSubtract()!=null){
			sb.append(",#{subtract}");
		}
		if(goods.getPostFree()!=null){
			sb.append(",#{postFree}");
		}
		if(goods.getIsHomePage()!=null){
			sb.append(",#{isHomePage}");
		}
		if(goods.getBuyCount()!=null){
			sb.append(",#{buyCount}");
		}
		if(goods.getIntrUrl()!=null){
			sb.append(",#{intrUrl}");
		}
		if(goods.getIsExpress()!=null){
			sb.append(",#{isExpress}");
		}
		if(goods.getGoodsType()!=null){
			sb.append(",#{goodsType}");
		}
		if(goods.getIsUseCoupon()!=null){
			sb.append(",#{isUseCoupon}");
		}
		if(goods.getManufacturer()!=null){
			sb.append(",#{manufacturer}");
		}
		if(goods.getApprovalNumber()!=null){
			sb.append(",#{approvalNumber}");
		}
		if(goods.getBrand()!=null){
			sb.append(",#{brand}");
		}
		if(goods.getMaterial()!=null){
			sb.append(",#{material}");
		}
		if(goods.getColour()!=null){
			sb.append(",#{colour}");
		}
		sb.append(")");
		System.out.println(sb.toString());
		return sb.toString();
	}
	
	public String findGoodsBySupplierId(Map<String, Object> map){
		StringBuffer sb = new StringBuffer();
		Integer isOnsale = (Integer) map.get("isOnsale");
		sb.append("select * ");
		sb.append(" from goods where supplier_id=#{supplierId} ");
		if( isOnsale != null && isOnsale >= 0){
			sb.append(" and is_onsale=#{isOnsale}");
		}
		sb.append(" order by last_modify_time desc limit #{start},#{limit}");
		return sb.toString();
	}
	
	/**
 	* 更新商品 
 	*/
	public String update(Goods domain){
		StringBuilder sb = new StringBuilder();
		
		sb.append("update goods set last_modify_time = now() ");
		
		if(domain.getQrCode()!=null){
			sb.append(",qr_code=#{qrCode}");
		}
		if(domain.getCatId()!=null){
			sb.append(",cat_id=#{catId}");
		}
		if(domain.getTitle()!=null){
			sb.append(",title=#{title}");
		}
		if(domain.getModel()!=null){
			sb.append(",model=#{model}");
		}
		if(domain.getSku()!=null){
			sb.append(",sku=#{sku}");
		}
		if(domain.getSupplierId()!=null){
			sb.append(",supplier_id=#{supplierId}");
		}
		if(domain.getStockId()!=null){
			sb.append(",stock_id=#{stockId}");
		}
		if(domain.getLocation()!=null){
			sb.append(",location=#{location}");
		}
		if(domain.getSubject()!=null){
			sb.append(",subject=#{subject}");
		}
		if(domain.getDescription()!=null){
			sb.append(",description=#{description}");
		}
		if(domain.getMarketPrice()!=null){
			sb.append(",market_price=#{marketPrice}");
		}
		if(domain.getSalePrice()!=null){
			sb.append(",sale_price=#{salePrice}");
		}
		if(domain.getAmount()!=null){
			sb.append(",amount=#{amount}");
		}
		if(domain.getIsReal()!=null){
			sb.append(",is_real=#{isReal}");
		}
		if(domain.getSortOrder()!=null){
			sb.append(",sort_order=#{sortOrder}");
		}
		if(domain.getIsHot()!=null){
			sb.append(",is_hot=#{isHot}");
		}
		if(domain.getIsRecommend()!=null){
			sb.append(",is_recommend=#{isRecommend}");
		}
		if(domain.getThumbnailImage()!=null){
			sb.append(",thumbnail_image=#{thumbnailImage}");
		}
		if(domain.getStockStatus()!=null){
			sb.append(",stock_status=#{stockStatus}");
		}
		if(domain.getIsOnsale()!=null){
			sb.append(",is_onsale=#{isOnsale}");
		}
		if(domain.getSubtract()!=null){
			sb.append(",subtract=#{subtract}");
		}
		if(domain.getPostFree()!=null){
			sb.append(",post_free=#{postFree}");
		}
		if(domain.getIsHomePage()!=null){
			sb.append(",is_home_page=#{isHomePage}");
		}
		if(domain.getBuyCount()!=null){
			sb.append(",buy_count=#{buyCount}");
		}
		if(domain.getIntrUrl()!=null){
			sb.append(",intr_url=#{intrUrl}");
		}
		if(domain.getIsExpress()!=null){
			sb.append(",is_express=#{isExpress}");
		}
		if(domain.getGoodsType()!=null){
			sb.append(",goods_type=#{goodsType}");
		}
		if(domain.getIsUseCoupon()!=null){
			sb.append(",is_use_coupon=#{isUseCoupon}");
		}
		if(domain.getManufacturer()!=null){
			sb.append(",manufacturer=#{manufacturer}");
		}
		if(domain.getApprovalNumber()!=null){
			sb.append(",approval_number=#{approvalNumber}");
		}
		if(domain.getBrand()!=null){
			sb.append(",brand=#{brand}");
		}
		if(domain.getColour()!=null){
			sb.append(",colour=#{colour}");
		}
		if(domain.getMaterial()!=null){
			sb.append(",material=#{material}");
		}
		sb.append(" where id=#{id}");
		
		return sb.toString();
	}

}
